---
title: "SQL-PRV-02: Provider collection filtered by tax-ID list"
summary: Retrieve multiple providers matching a tax-ID list
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-08-21
library: SAIF.Business.View.Provider
reference_type: embedded
---

# SQL-PRV-02 Provider collection filtered by tax-ID list

Retrieves the set of `PROVIDER` records whose primary tax ID appears in a caller-supplied list.
The `IN` list is built by direct string concatenation (`String.Format("'{0}',", taxId)`) rather
than bind parameters.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-PRV-02 |
| **Operation type** | Read |
| **Execution method** | Inline SQL (BOF criteria constant, string-concatenated predicate) |
| **Database** | Oracle |
| **Schema** | `[unqualified]**` |
| **Command type** | Text |
| **Business object** | `clsProviderCollection` |
| **Criteria property** | `RetrieveSQL` |
| **Lifecycle method** | `BeforeRetrieve` / `AfterRetrieve` |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [PROVIDER](../database-tables.md#provider) | SELECT | `PRIMARY_TAX_ID`, `PROVIDER_SEQ_NUM`, `UPDATE_DATE`, `UPDATE_NUM`, `UPDATE_USER` |
| [PROVIDER_TAX_ID](../database-tables.md#provider_tax_id) | JOIN (LEFT OUTER) | `TAX_ID`, `TAX_ID_DISPLAY_DESCR`, `REGISTERED_NAME` |

## Sample SQL

> **Security note:** the `IN` list is produced by `String.Format("'{0}',", taxId)` and
> concatenated directly into the statement; no bind parameters are used ([BR-PRV-003](../business-rules.md#br-prv-003-provider-set-retrieval-uses-unparameterized-sql)).

```sql
-- Provider collection filtered by a tax-ID list
SELECT
  PRV.PRIMARY_TAX_ID        AS PrimaryTaxID,
  PRV.PROVIDER_SEQ_NUM      AS ProviderID,
  PRV.UPDATE_DATE           AS UpdateDate,
  PRV.UPDATE_NUM            AS UpdateNum,
  PRV.UPDATE_USER           AS UpdateUser,
  TAX.TAX_ID_DISPLAY_DESCR  AS FormattedPrimaryTaxID,
  TAX.REGISTERED_NAME       AS RegisteredName
FROM PROVIDER PRV
  LEFT OUTER JOIN PROVIDER_TAX_ID TAX
    ON TAX.TAX_ID = PRV.PRIMARY_TAX_ID
WHERE prv.primary_tax_id IN ('123456789', '987654321')
```

## Operations and processes
- [Retrieve provider set](../operations/retrieve-provider-set.md)

## Business rules
- [BR-PRV-003: Unparameterized provider collection SQL](../business-rules.md#br-prv-003-provider-set-retrieval-uses-unparameterized-sql)

## Sources
- SAIF.Business.View.Provider/clsProviderCollection.vb